﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OLTS.DATA_SERVICE;
using OLTS.MODEL;
using OLTS.MODEL.Custom_Model;
using CarlosAg.ExcelXmlWriter;
using System.Drawing;
using OLTS.WEB.BaseClass;
using OfficeOpenXml.Drawing;
using OfficeOpenXml;

namespace ONTS.WEB.Views
{
    public partial class ExportExcel : BaseWebpage
    {
        string strEmpid = "";
        int year;
        int month;

        public static string ToMonthName(int mon)
        {
            return CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(mon);
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["LOGIN"] != null)
            {
                OTS_LOGIN model = BaseService.LOGIN;
                var role = model.ROLE.Split(':');
                if (role.Contains("administrator"))
                {
                    ddlYear.Enabled = true;
                    ddlMonth.Enabled = true;
                }
                else
                {
                    ddlYear.Enabled = false;
                    ddlMonth.Enabled = false;
                }
            }
            else
            {
                Response.Redirect("/AccessDeniedPage.aspx");
            }


            strEmpid = Request.QueryString["id"].ToString();
            if (!IsPostBack)
            {
                loadTime();
                var cmd = new Project_Service();
                var obj = cmd.GetByID(strEmpid);
                var timesheet = obj.OTS_SITE.TIMESHEET_FORMAT;
                if (timesheet == "3")
                {
                    ddlWeek.Visible = false;
                }
                else if (timesheet == "1" || timesheet == "2")
                {
                    ddlWeek.Visible = true;
                }
            }
        }

        private void loadTime()
        {
            ddlYear.Items.Add(new ListItem(DateTime.Now.Year.ToString(), DateTime.Now.Year.ToString()));
            ddlYear.Items.Add(new ListItem((DateTime.Now.Year - 1).ToString(), (DateTime.Now.Year - 1).ToString()));

            ddlYear.SelectedValue = DateTime.Now.Year.ToString();

            ddlMonth.Items.Add(new ListItem(ToMonthName(1), "1"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(2), "2"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(3), "3"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(4), "4"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(5), "5"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(6), "6"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(7), "7"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(8), "8"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(9), "9"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(10), "10"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(11), "11"));
            ddlMonth.Items.Add(new ListItem(ToMonthName(12), "12"));

            ddlMonth.SelectedValue = System.DateTime.Now.Month.ToString();

            ddlMonth.Enabled = false;
            ddlYear.Enabled = false;
            if (Session["LOGIN"] != null)
            {
                OTS_LOGIN model = (OTS_LOGIN)Session["LOGIN"];
                var role = model.ROLE.Split(':');
                if (role.Contains("administrator"))
                {
                    ddlYear.Enabled = true;
                    ddlMonth.Enabled = true;
                }
            }
            loadPeriod();
        }

        protected void ddlMonth_SelectedIndexChanged(object sender, EventArgs e)
        {
            loadPeriod();
        }

        private void loadPeriod()
        {
            ddlWeek.Items.Clear();
            if (ddlMonth.SelectedValue != "0")
            {
                year = Convert.ToInt16(ddlYear.SelectedValue);
                month = Convert.ToInt16(ddlMonth.SelectedValue);
                var cal = System.Globalization.CultureInfo.CurrentCulture.Calendar;
                IEnumerable<int> daysInMonth = Enumerable.Range(1, cal.GetDaysInMonth(year, month));
                int countId = 0;
                string MonthVal = "";
                string monthShow = "";
                string Lastday = "";
                string LastVal = "";
                foreach (int day in daysInMonth)
                {
                    DateTime date = new DateTime(year, month, day);
                    if (countId == 0)
                    {
                        monthShow = date.ToString("dd MMM yyyy");
                        MonthVal = date.Day.ToString();
                        countId++;
                    }
                    else if (date.DayOfWeek == DayOfWeek.Monday)
                    {
                        monthShow = date.ToString("dd MMM yyyy");
                        MonthVal = date.Day.ToString();
                    }
                    else if (date.DayOfWeek == DayOfWeek.Friday)
                    {
                        monthShow = "Week :" + countId + " " + monthShow + "," + date.ToString("dd MMM yyyy");
                        MonthVal = month + "," + MonthVal + "," + date.Day;
                        ddlWeek.Items.Add(new ListItem(monthShow, MonthVal));
                        Lastday = "";
                        LastVal = "";
                        countId++;
                    }
                    else if ((daysInMonth.ToList().Count - day) < 4)
                    {
                        if (date.DayOfWeek != DayOfWeek.Sunday && date.DayOfWeek != DayOfWeek.Saturday)
                        {
                            Lastday = "Week :" + countId + " " + monthShow + "," + date.ToString("dd MMM yyyy");
                            LastVal = month + "," + MonthVal + "," + date.Day;
                        }
                    }
                }
                if (Lastday.Length > 0)
                {
                    ddlWeek.Items.Add(new ListItem(Lastday, LastVal));
                }
            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            var serv = new Export_To_Excel_TimeSheet();
            string[] arr = ddlWeek.SelectedValue.ToString().Split(',');
            year = Convert.ToInt16(ddlYear.SelectedValue);
            month = Convert.ToInt16(ddlMonth.SelectedValue);
            var s_date = Convert.ToInt16(arr[1]);
            var e_date = Convert.ToInt16(arr[2]);
            var detail = serv.getWORK_DETAIL(strEmpid, month, s_date, e_date);
            //detail = detail.OrderBy(x => x.DATE).ToList();
            var siteid = BaseService.PROJECT.SITE_ID;

            var site = serv.getSite(strEmpid);

            if (site.TIMESHEET_FORMAT == "1")
            {
                ExportBot(detail, site, ddlWeek.SelectedValue.ToString());
                //LoadBot(detail, site, ddlWeek.SelectedValue.ToString());
            }
            else if (site.TIMESHEET_FORMAT == "2")
            {
                ExportCUBENew(detail, site, ddlWeek.SelectedValue.ToString());
                //ExportCUBE(detail, site, ddlWeek.SelectedValue.ToString());
                //LoadOther(detail, site, ddlWeek.SelectedValue.ToString());
            }
            else if (site.TIMESHEET_FORMAT == "3" || siteid == 8)
            {
                detail = serv.getWORK_DETAIL(strEmpid, month);
                LoadTbank(detail, site, ddlWeek.SelectedValue.ToString());
            }

        }

        private void LoadTbank(List<OTS_TIME_SHEET> detail, Emp_On_Proj_Detail site, string WeekValue)
        {
            year = Convert.ToInt16(ddlYear.SelectedValue);
            month = Convert.ToInt16(ddlMonth.SelectedValue);
            string str = "<table id='tbl' border='0' runat='server' >";
            var monthName = new DateTime(year, month, 01).ToString("MMMM");
            str += "<tr> <td  style='font-weight: bold;' >Outsource Name</td> <td  style='font-weight: bold;' >" + site.STAFF_NAME_TH;
            str += " </td> <td style='font-weight: bold;' > As of month : </td> <td  style='font-weight: bold;' >" + monthName + " Year : " + year;
            str += "</td> <td></td>  <td></td> <td></td> <td></td> <td></td> <td></td></tr> ";

            var cal = System.Globalization.CultureInfo.CurrentCulture.Calendar;
            IEnumerable<int> daysInMonth = Enumerable.Range(1, cal.GetDaysInMonth(year, month));

            int WorkDay = 0;
            int LeaveDay = 0;

            foreach (var p in detail)
            {

                foreach (var c in p.OTS_WORK_DETAIL)
                {
                    if (c.LEAVE_ID != null && c.AMOUNT_OF_LEAVE == 8)
                    {
                        LeaveDay = LeaveDay + 1;
                    }
                    else if ((c.LEAVE_ID == 0 || c.LEAVE_ID == null) && c.AMOUNT_OF_TIME == 8)
                    {
                        WorkDay = WorkDay + 1;
                    }
                }

                var obj = p.OTS_WORK_DETAIL.Where(x => x.LEAVE_ID == null).FirstOrDefault();
            }

            var totalworkingday = 0;
            foreach (int day in daysInMonth)
            {
                DateTime date = new DateTime(year, month, day);
                if (date.DayOfWeek != DayOfWeek.Sunday && date.DayOfWeek != DayOfWeek.Saturday)
                {
                    if (!Utility.isHoliday(date))
                    {
                        totalworkingday = totalworkingday + 1;
                    }

                }
            }

            str += "<tr> <td colspan='2'  style='font-weight: bold;' >จำนวนวันทำงาน " + totalworkingday + " วัน</td> <td colspan='2'";
            str += "style='font-weight: bold;' > จำนวนวันมาทำงาน " + WorkDay + " วัน</td> </td> <td colspan='2'  style='font-weight:bold;' >";
            str += "จำนวนวันลา " + LeaveDay + " วัน</td> <td></td> <td></td> <td></td> <td></td></tr> ";
            str += "<tr> <td style='text-align:center; font-weight: bold; border: 1px solid black;'>Date</td> <td style='text-align:center; font-weight: bold; border: 1px solid black;'>Request Id.</td><td style='text-align:center; border: 1px solid black; font-weight: bold;'>Application ID</td> <td style='text-align:center; border: 1px solid black; font-weight: bold;'>Application Name</td><td style='text-align:center; border: 1px solid black; font-weight: bold;'>Function name</td><td style='text-align:center; border: 1px solid black; font-weight: bold;'>รายละเอียดที่ทำ</td><td colspan='2' style='text-align:center; border: 1px solid black; font-weight: bold;'>จำนวน Man hour</td><td style='text-align:center; border: 1px solid black; font-weight: bold;'>Role</td><td style='text-align:center; border: 1px solid black; font-weight: bold;'>ปัญหา/อุปสรรค/หมายเหตุ</td>   </tr> ";
            str += "<tr> <td style='border: 1px solid black;'></td> <td style='border: 1px solid black;'> </td><td style='border: 1px solid black;'></td> <td style='border: 1px solid black;'>";
            str += "</td><td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td> <td  style='border: 1px solid black; text-align:center; '>";
            str += "ในเวลาทำงาน <br/>(8:30-17:00)</td><td style='border: 1px solid black; text-align:center; '>นอกเวลา<br/>งาน</td><td style='border: 1px solid black;'></td>  <td style='border: 1px solid black;'></td> </tr> ";



            foreach (int day in daysInMonth)
            {
                DateTime date = new DateTime(year, month, day);
                if (date.DayOfWeek == DayOfWeek.Sunday || date.DayOfWeek == DayOfWeek.Saturday)
                {
                    str += "<tr><td  style='text-align:center; border: 1px solid black; background-color: gray;'>" + getFullMonthDate(date) + "</td> <td style='border: 1px solid black; background-color: gray;'></td> <td style='border: 1px solid black; background-color: gray;'></td> <td style='border: 1px solid black; background-color: gray;'></td> <td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black; background-color: gray;'></td> </tr> ";
                }
                else
                {
                    string WorkDesc = "";
                    string Function = "";
                    Decimal WorkTime = 0;
                    Decimal WorkOverTime = 0;
                    string Role = "";
                    string Remark = "";
                    string app_id = "";
                    string Request_id = "";
                    string app_name = "";
                    foreach (OLTS.MODEL.OTS_TIME_SHEET sheet in detail)
                    {
                        if (sheet.DATE == date)
                        {

                            foreach (OLTS.MODEL.OTS_WORK_DETAIL det in sheet.OTS_WORK_DETAIL)
                            {
                                Request_id = det.REQUEST_ID;
                                WorkDesc += det.DETAIL + ",";
                                Function = det.FUNCTION_NAME;
                                WorkTime += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                WorkOverTime += Convert.ToDecimal(det.AMOUNT_OF_TIME_OVER_TIME);
                                Role = det.ROLE;
                                Remark = det.REMARK + ",";
                                app_id = det.APPLICATION_ID;
                                app_name = det.APPLICATION_NAME;
                            }
                        }
                    }
                    if (WorkDesc.Length > 0)
                    {
                        WorkDesc = WorkDesc.Substring(0, WorkDesc.Length - 1);
                    }
                    if (Remark.Length > 0)
                    {
                        Remark = Remark.Substring(0, Remark.Length - 1);
                    }
                    str += "<tr><td  style='text-align:center; border: 1px solid black;'>" + getFullMonthDate(date) + "</td>  <td style='text-align:center; border: 1px solid black;'>" + Request_id + "</td> <td style='text-align:center; border: 1px solid black;'>" + app_id + "</td><td style=' border: 1px solid black;'>" + app_name + "</td> <td style=' border: 1px solid black;'>" + Function + "</td> <td style=' border: 1px solid black;'>" + WorkDesc + "</td><td style='text-align:center; border: 1px solid black;'>" + WorkTime + "</td> <td style='text-align:center; border: 1px solid black;'>" + WorkOverTime + "</td> <td style='text-align:center; border: 1px solid black;'>" + Role + "</td><td style=' border: 1px solid black;'>" + Remark + "</td> </tr> ";

                }
            }
            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td></td><td></td> <td></td><td></td></tr> ";
            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td></td><td></td> <td></td><td></td></tr> ";
            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td></td><td></td> <td></td><td></td></tr> ";

            str += "<tr> <td colspan='3' style='text-align:left; '>_____________________________</td>  <td colspan='4' style='text-align:center; '>________________________________________</td> <td colspan='3' style='text-align:right; '>_____________________________</td></tr> ";
            str += "<tr> <td colspan='3' style='text-align:left; '>Cube of 9</td>  <td colspan='4' style='text-align:center; '>Outsource Name</td> <td colspan='3' style='text-align:right; '>Thanachart</td></tr> ";

            str += " </table>";
            Export_file(str, "TBank_" + site.STAFF_NAME_EN);
        }

        private void ExportBot(List<OTS_TIME_SHEET> detail, Emp_On_Proj_Detail site, string WeekValue)
        {
            string[] strWeek = WeekValue.Split(',');
            detail = detail.Where(x => Convert.ToDateTime(x.DATE).Month == Convert.ToInt16(strWeek[0]) && (Convert.ToDateTime(x.DATE).Day >= Convert.ToInt16(strWeek[1]) && Convert.ToDateTime(x.DATE).Day <= Convert.ToInt16(strWeek[2]))).ToList();

            var book = new Workbook();
            book.ExcelWorkbook.ActiveSheetIndex = 0;
            book.ExcelWorkbook.WindowTopX = 100;
            book.ExcelWorkbook.WindowTopY = 200;
            book.ExcelWorkbook.WindowHeight = 7000;
            book.ExcelWorkbook.WindowWidth = 8000;
            book.Properties.Author = "Administrator";
            book.Properties.Title = "Excel Export";
            book.Properties.Created = DateTime.Now;

            ExcelStyle.BOT_StyleConfiguration(book);

            var wrRow = new WorksheetRow();
            var wrCell = new WorksheetCell();

            Worksheet wsSheet = wsSheet = book.Worksheets.Add("BOT Timesheet " + strWeek[1] + "-" + strWeek[2]);
            var s_date = getFullMonthDate(Convert.ToDateTime(detail[0].DATE));
            var e_date = getFullMonthDate(Convert.ToDateTime(detail[detail.Count - 1].DATE));

            BOT_WriteHeaderTopic(wsSheet, wrRow, wrCell, s_date, e_date);

            BOT_WriteHeaderTable(wsSheet, wrRow, wrCell, site.STAFF_NAME_TH, site.PROJECT_NAME);

            decimal sumHour = 0;

            foreach (var sheet in detail)
            {
                var workingdate = getWorkingDate(Convert.ToDateTime(sheet.DATE));
                wrRow = wsSheet.Table.Rows.Add();
                wrRow.Cells.Add(workingdate, DataType.String, "body");
                wrRow.Height = 33;
                decimal WorkHour = 0;
                decimal WorkOt = 0;
                string Workdesc = "";

                foreach (var detial in sheet.OTS_WORK_DETAIL)
                {
                    sumHour += Convert.ToDecimal(detial.AMOUNT_OF_TIME);
                    WorkHour += Convert.ToDecimal(detial.AMOUNT_OF_TIME);
                    WorkOt += Convert.ToDecimal(detial.AMOUNT_OF_TIME_OVER_TIME);
                    Workdesc += "\r\n" + detial.DETAIL;
                }

                wrRow.Cells.Add(getWorkingTime(sheet.OTS_WORK_DETAIL.ToList()), DataType.String, "body");
                wrRow.Cells.Add(WorkHour.ToString(), DataType.Number, "body");
                wrRow.Cells.Add(WorkOt.ToString(), DataType.Number, "body");
                wrCell = wrRow.Cells.Add(Workdesc, DataType.String, "bodyleft");
                wrCell.MergeAcross = 2;
            }

            BOT_WriteFooter(wsSheet, wrRow, wrCell, site.STAFF_NAME_TH, site.PROJECT_NAME, detail.Count);

            var sumRow = new WorksheetRow();
            var sumCell = new WorksheetCell();
            Worksheet sumSheet = book.Worksheets.Add("BOT Summary Sheet");
            sumRow = sumSheet.Table.Rows.Add();
            BOT_SumColumnConfiguration(sumSheet);

            sumRow = sumSheet.Table.Rows.Add();
            sumCell = sumRow.Cells.Add("รายงานสรุปผลงาน", DataType.String, "header");
            sumCell.MergeAcross = 5;
            sumRow.Height = 20;

            sumRow = sumSheet.Table.Rows.Add();
            sumCell = sumRow.Cells.Add("บริษัท คิวบ์ออฟไนน์ จำกัด", DataType.String, "header");
            sumCell.MergeAcross = 5;
            sumRow.Height = 20;

            sumRow = sumSheet.Table.Rows.Add();
            sumCell = sumRow.Cells.Add("ประจำวันที่ " + getFullMonthDate(Convert.ToDateTime(detail[0].DATE)) + " ถึง " + getFullMonthDate(Convert.ToDateTime(detail[detail.Count - 1].DATE)), DataType.String, "header");
            sumCell.MergeAcross = 5;
            sumRow.Height = 20;
            sumRow = sumSheet.Table.Rows.Add();

            BOT_AddEmtypyCell(4, sumRow, "header");

            sumRow.Cells.Add("วันที่รายงาน", DataType.String, "header");
            sumRow.Cells.Add(getFullMonthDate(DateTime.Now), DataType.String, "headernotbold");

            sumRow = sumSheet.Table.Rows.Add();
            sumRow.Cells.Add("ชื่อ - นามสกุล", DataType.String, "header");
            sumRow.Cells.Add(site.STAFF_NAME_TH, DataType.String, "headernotbold");
            sumRow.Cells.Add("ประเภทงาน", DataType.String, "header");
            sumCell = sumRow.Cells.Add(site.PROJECT_NAME, DataType.String, "headernotbold");
            sumCell.MergeAcross = 2;
            sumRow.Height = 20;

            sumRow = sumSheet.Table.Rows.Add();
            sumRow.Cells.Add("ลำดับที่", DataType.String, "body");
            sumRow.Cells.Add("ขั้นตอนของงาน", DataType.String, "body");
            sumRow.Cells.Add("งานที่ส่งมอบ", DataType.String, "body");
            sumRow.Cells.Add("สถานะของงาน", DataType.String, "body");
            sumRow.Cells.Add("วันที่แล้วเสร็จ", DataType.String, "body");
            sumRow.Cells.Add("หมายเหตุ", DataType.String, "body");
            int i = 0;
            foreach (var sheet in detail)
            {
                var leaveObj = sheet.OTS_WORK_DETAIL.Where(x => x.LEAVE_ID == null).FirstOrDefault();
                if (leaveObj != null)
                {
                    i = i + 1;
                    sumRow = sumSheet.Table.Rows.Add();
                    string Workdesc = ""; string remark = "";
                    var successDate = getWorkingDate(Convert.ToDateTime(sheet.DATE));
                    foreach (var o in sheet.OTS_WORK_DETAIL.Where(x => x.LEAVE_ID == null).ToList())
                    {
                        Workdesc += "\r\n" + o.DETAIL;
                        remark += "\r\n" + o.REMARK;
                    }
                    sumRow.Cells.Add(i.ToString(), DataType.Number, "body");
                    sumRow.Cells.Add(Workdesc, DataType.String, "bodyleft");
                    sumRow.Cells.Add("", DataType.String, "bodyleft");
                    sumRow.Cells.Add(site.JOB_STATUS, DataType.String, "body");
                    sumRow.Cells.Add(successDate, DataType.String, "body");
                    sumRow.Cells.Add(remark, DataType.String, "bodyleft");
                }

            }
            sumRow = sumSheet.Table.Rows.Add();
            sumRow = sumSheet.Table.Rows.Add();
            sumRow = sumSheet.Table.Rows.Add();

            exportNew(book, site.STAFF_NAME_EN);
        }

        private void ExportCUBENew(List<OTS_TIME_SHEET> detail, Emp_On_Proj_Detail site, string WeekValue)
        {
            string path = MapPath("../Utility/template.xlsx");
            ExportExcel_New((CreateContentCUBE(path, detail, site, WeekValue)), site.STAFF_NAME_EN);

        }

        public byte[] CreateContentCUBE(string path, List<OTS_TIME_SHEET> detail, Emp_On_Proj_Detail site, string WeekValue)
        {
            FileInfo newFile = new FileInfo(@"../Utility/target.xlsx");
            FileInfo template = new FileInfo(path);

            using (ExcelPackage package = new ExcelPackage(newFile, template))
            {
                string[] strWeek = WeekValue.Split(',');
                detail = detail.Where(x => Convert.ToDateTime(x.DATE).Month == Convert.ToInt16(strWeek[0]) && (Convert.ToDateTime(x.DATE).Day >= Convert.ToInt16(strWeek[1]) && Convert.ToDateTime(x.DATE).Day <= Convert.ToInt16(strWeek[2]))).ToList();
                string dateFrm = getFullMonthDate(year, month, Convert.ToInt16(strWeek[1]));
                string dateTo = getFullMonthDate(year, month, Convert.ToInt16(strWeek[2]));

                // Create worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
                worksheet.Cells[3, 2].Value = site.STAFF_NAME_EN;
                worksheet.Cells[3, 6].Value = site.SITE_NAME;
                worksheet.Cells[4, 2].Value = site.STAFF_ID;
                worksheet.Cells[5, 2].Value = site.STAFF_POSITION;

                worksheet.Cells[11, 1].Value = site.PROJECT_NAME;
                worksheet.Cells[28, 1].Value = site.STAFF_NAME_EN;
                worksheet.Cells[28, 4].Value = site.PROJECT_OWN;
                worksheet.Cells[28, 9].Value = site.PROJECT_OWN_1;

                int r = 10;
                int c = 5; 
                int col_leave = 5;
                foreach (var sheet in detail)
                {
                    r = r + 1;
                    c = c + 1; 
                    col_leave = col_leave + 1;
                    worksheet.Cells[r, 2].Value = getJobCode(sheet.OTS_WORK_DETAIL.ToList());
                    worksheet.Cells[r, 3].Value = getDescription(sheet.OTS_WORK_DETAIL.ToList());
                    worksheet.Cells[r, 12].Value = getRemark(sheet.OTS_WORK_DETAIL.ToList());
                    var work_hr = getAmonthTotal(sheet.OTS_WORK_DETAIL.ToList());
                    if (work_hr != "")
                    {
                        worksheet.Cells[r, c].Value = Utility.CheckInt(work_hr);
                    }
                    else
                    {
                        worksheet.Cells[r, c].Value = work_hr;
                    }

                    var leave_hr = getAmontOfLeave(sheet.OTS_WORK_DETAIL.ToList());
                    if (leave_hr != "")
                    {
                        var leave_tp = getLeaveTypeID(sheet.OTS_WORK_DETAIL.ToList());
                        if (leave_tp == "1")
                        {
                            worksheet.Cells[18, col_leave].Value = Utility.CheckInt(leave_hr);
                        }
                        else if (leave_tp == "2")
                        {
                            worksheet.Cells[19, col_leave].Value = Utility.CheckInt(leave_hr);
                        }
                        else if (leave_tp == "3")
                        {
                            worksheet.Cells[20, col_leave].Value = Utility.CheckInt(leave_hr);
                        }
                        
                    }
                }

                #region " StartTime and EndTime"

                c = 5;
                foreach (var sheet in detail)
                {
                    if (c == 5)
                    {
                        var sunday = getWorkingDateCube(Convert.ToDateTime(sheet.DATE).AddDays(-1));
                        worksheet.Cells[10, 5].Value = sunday;
                    }

                    c = c + 1;
                    var obj = sheet.OTS_WORK_DETAIL.ToList();
                    if (obj.Count > 0)
                    {
                        var stime = obj.First();
                        worksheet.Cells[7, c].Value = stime.START_TIME;
                    }
                    else
                    {
                        worksheet.Cells[7, c].Value = "9.00";
                    }
                }

                c = 5;
                foreach (var sheet in detail)
                {
                    c = c + 1;
                    var obj = sheet.OTS_WORK_DETAIL.ToList();
                    if (obj.Count > 0)
                    {
                        var stime = obj.First();
                        worksheet.Cells[8, c].Value = stime.END_TIME;
                    }
                    else
                    {
                        worksheet.Cells[8, c].Value = "18.00";
                    }
                }

                #endregion

                return package.GetAsByteArray();
            }
        }

        private void ExportCUBE(List<OTS_TIME_SHEET> detail, Emp_On_Proj_Detail site, string WeekValue)
        {
            string[] strWeek = WeekValue.Split(',');
            detail = detail.Where(x => Convert.ToDateTime(x.DATE).Month == Convert.ToInt16(strWeek[0]) && (Convert.ToDateTime(x.DATE).Day >= Convert.ToInt16(strWeek[1]) && Convert.ToDateTime(x.DATE).Day <= Convert.ToInt16(strWeek[2]))).ToList();

            var book = new Workbook();

            BookConfiguration(book);
            StyleConfiguration(book);

            year = Convert.ToInt16(ddlYear.SelectedValue);
            month = Convert.ToInt16(ddlMonth.SelectedValue);
            string dateFrm = getFullMonthDate(year, month, Convert.ToInt16(strWeek[1]));
            string dateTo = getFullMonthDate(year, month, Convert.ToInt16(strWeek[2]));
            var wrRow = new WorksheetRow();
            var wrCell = new WorksheetCell();
            Worksheet wsSheet = wsSheet = book.Worksheets.Add("Timesheet " + strWeek[1] + "-" + strWeek[2]);

            HeaderConfiguration(wsSheet, wrRow, wrCell);

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("Staff Name", DataType.String, "headerleft");
            wrCell = wrRow.Cells.Add(site.STAFF_NAME_TH, DataType.String, "bodynoborderleft");
            wrCell.MergeAcross = 1;
            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("Client", DataType.String, "headerleft");
            wrRow.Cells.Add(site.SITE_NAME, DataType.String, "bodynoborderleft");

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("Staff ID", DataType.String, "headerleft");
            wrCell = wrRow.Cells.Add(site.STAFF_ID, DataType.String, "bodynoborderleft");
            wrCell.MergeAcross = 1;

            ImageLogo(book, wrRow);

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("Position", DataType.String, "headerleft");
            wrCell = wrRow.Cells.Add(site.STAFF_POSITION, DataType.String, "bodynoborderleft");
            wrCell.MergeAcross = 1;
            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("Week of", DataType.String, "headerleft");
            wrRow.Cells.Add(getWorkingDateCube(Convert.ToDateTime(dateFrm)), DataType.String, "bodyleft");
            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("TO", DataType.String, "headerleft");
            wrRow.Cells.Add(getWorkingDateCube(Convert.ToDateTime(dateTo)), DataType.String, "bodyleft");
            wrRow = wsSheet.Table.Rows.Add();
            wrRow = wsSheet.Table.Rows.Add();
            wrCell = wrRow.Cells.Add("WEEKLY", DataType.String, "header");
            wrCell.MergeAcross = 2;
            wrRow.Cells.Add("Time In", DataType.String, "headerborderleft");
            wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");

            foreach (var sheet in detail)
            {
                var obj = sheet.OTS_WORK_DETAIL.ToList();
                if (obj.Count > 0)
                {
                    var stime = obj.First();
                    wrRow.Cells.Add(stime.START_TIME, DataType.String, "body");
                }
                else
                {
                    wrRow.Cells.Add("9.00", DataType.String, "body");
                }
            }

            var day = detail.Count;

            wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");

            wrRow = wsSheet.Table.Rows.Add();
            wrCell = wrRow.Cells.Add("TIMESHEET", DataType.String, "headerborderleft");
            wrCell.MergeAcross = 2;
            wrRow.Cells.Add("Time Out", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");

            foreach (var sheet in detail)
            {
                var obj = sheet.OTS_WORK_DETAIL.ToList();
                if (obj.Count > 0)
                {
                    var stime = obj.First();
                    wrRow.Cells.Add(stime.END_TIME, DataType.String, "body");
                }
                else
                {
                    wrRow.Cells.Add("18.00", DataType.String, "body");
                }
            }

            wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("Project", DataType.String, "headerborderleft");
            wrRow.Cells.Add("Job", DataType.String, "headerborderleft");
            wrRow.Cells.Add("Description", DataType.String, "headerborderleft");
            wrRow.Cells.Add("Total", DataType.String, "headercenborder");
            wrRow.Cells.Add("Sun", DataType.String, "Grey_Cen_Bold_Top_Botton");
            wrRow.Cells.Add("Mon", DataType.String, "headercenborder");
            wrRow.Cells.Add("Tue", DataType.String, "headercenborder");
            wrRow.Cells.Add("Wed", DataType.String, "headercenborder");
            wrRow.Cells.Add("Thu", DataType.String, "headercenborder");
            wrRow.Cells.Add("Fri", DataType.String, "headercenborder");
            wrRow.Cells.Add("Sat", DataType.String, "Grey_Cen_Bold_Top_Botton");
            wrRow.Cells.Add("Remark", DataType.String, "headercenborder");

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("", DataType.String, "headerborderleft");
            wrRow.Cells.Add("Code", DataType.String, "headerborderleft");
            wrRow.Cells.Add("", DataType.String, "headerborderleft");
            wrRow.Cells.Add("Hours", DataType.String, "headercenborder");
            var sun = getWorkingDateCube(Convert.ToDateTime(detail.First().DATE).AddDays(-1));
            wrRow.Cells.Add(sun, DataType.String, "Grey_Cen_Top_Botton");

            foreach (var sheet in detail)
            {
                var dt = getWorkingDateCube(Convert.ToDateTime(sheet.DATE));
                wrRow.Cells.Add(dt, DataType.String, "body");
            }

            var sat = getWorkingDateCube(Convert.ToDateTime(detail.First().DATE).AddDays(1));
            wrRow.Cells.Add(sat, DataType.String, "Grey_Cen_Top_Botton");
            wrRow.Cells.Add("", DataType.String, "body");

            int row = 0;
            foreach (var sheet in detail)
            {
                row = row + 1;
                wrRow = wsSheet.Table.Rows.Add();
                if (row == 1)
                {
                    wrRow.Cells.Add(site.PROJECT_NAME, DataType.String, "Body_Left_BorderDash");
                    wrRow.Cells.Add("", DataType.String, "Body_Left_BorderDash");
                    var des = getDescription(sheet.OTS_WORK_DETAIL.ToList());
                    wrRow.Cells.Add(des, DataType.String, "Body_Left_BorderDash");

                    var sumcell = wrRow.Cells.Add();
                    sumcell.Formula = "=SUM(RC[7]:RC[1])";
                    sumcell.StyleID = "Body_Right_BorderDash";
                    wrRow.Cells.Add("", DataType.String, "Grey_Right_NoBoder");
                    wrRow.Cells.Add(sheet.OTS_WORK_DETAIL.First().AMOUNT_OF_TIME.ToString(), DataType.Number, "Body_Right_BorderDash");
                    addEmptyCellAfter(wrRow, row);
                }
                else
                {
                    wrRow.Cells.Add("", DataType.String, "Body_Left_BorderDash");
                    wrRow.Cells.Add("", DataType.String, "Body_Left_BorderDash");
                    var des = getDescription(sheet.OTS_WORK_DETAIL.ToList());
                    wrRow.Cells.Add(des, DataType.String, "Body_Left_BorderDash");

                    var sumcell = wrRow.Cells.Add();
                    sumcell.Formula = "=SUM(RC[7]:RC[1])";
                    sumcell.StyleID = "Body_Right_BorderDash";
                    addEmptyCellBefore(wrRow, row);
                    int total = sheet.OTS_WORK_DETAIL.ToList().Sum(x => Convert.ToInt32(x.AMOUNT_OF_TIME));
                    if (total == 0)
                    {
                        wrRow.Cells.Add("", DataType.String, "Body_Right_BorderDash");
                    }
                    else
                    {
                        wrRow.Cells.Add(total.ToString(), DataType.Number, "Body_Right_BorderDash");
                    }

                    addEmptyCellAfter(wrRow, row);
                }
            }

            getTotalofProject(wsSheet, wrRow, detail);
            getOnLeaveDetailFooter(wsSheet, wrRow, detail);
            getAnnualHolidayDetailFooter(wsSheet, wrRow, detail);
            getPublicHolidayDetailFooter(wsSheet, wrRow, detail);
            getTotalOfLeave(wsSheet, wrRow, detail);
            getGrandTotal(wsSheet, wrRow, detail);
            getSignature(wsSheet, wrRow, detail, wrCell, site.STAFF_NAME_TH, site.PROJECT_OWN, site.PROJECT_OWN_1);
            exportNew(book, site.STAFF_NAME_EN);

        }

        #region "BOT Configuration"

        private void BOT_WriteHeaderTopic(Worksheet wsSheet, WorksheetRow wrRow, WorksheetCell wrCell, string StartDate, string EndDate)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrRow = wsSheet.Table.Rows.Add();
            wrCell = wrRow.Cells.Add("รายงานการปฏิบัติงาน รายบุคคล", DataType.String, "header");
            wrCell.MergeAcross = 6;
            wrRow.Height = 25;

            wrRow = wsSheet.Table.Rows.Add();
            wrCell = wrRow.Cells.Add("บริษัท คิวบ์ออฟไนน์ จำกัด", DataType.String, "header");
            wrCell.MergeAcross = 6;
            wrRow.Height = 25;

            BOT_MainColumnConfiguration(wsSheet);

            wrRow = wsSheet.Table.Rows.Add();
            BOT_AddEmtypyCell(2, wrRow, "");
            wrRow.Cells.Add("ประจำวันที่", DataType.String, "header");
            wrRow.Cells.Add(StartDate, DataType.String, "header");
            wrRow.Cells.Add("ถึง", DataType.String, "header");
            wrRow.Cells.Add(EndDate, DataType.String, "header");
            wrRow.Cells.Add("");
            wrRow.Height = 25;

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Height = 10;

            wrRow = wsSheet.Table.Rows.Add();
            BOT_AddEmtypyCell(5, wrRow, "");
        }

        private void BOT_WriteHeaderTable(Worksheet wsSheet, WorksheetRow wrRow, WorksheetCell wrCell, string StaffName, string ProjectName)
        {
            wrRow.Cells.Add("วันที่รายงาน", DataType.String, "header");
            wrRow.Cells.Add(getFullMonthDate(DateTime.Now));
            wrRow.Height = 25;

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("ชื่อ - นามสกุล", DataType.String, "header");
            wrRow.Cells.Add(StaffName, DataType.String, "headernotbold");
            wrRow.Cells.Add("ประเภทงาน", DataType.String, "header");
            wrCell = wrRow.Cells.Add(ProjectName, DataType.String, "headernotbold");
            wrCell.MergeAcross = 3;
            wrRow.Height = 20;

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("วันที่", DataType.String, "body");
            wrRow.Cells.Add("เวลาปฏิบัติงาน(เริ่ม – เลิก)", DataType.String, "body");
            wrRow.Cells.Add("จำนวนเวลาปฏิบัติงานปกติ  (ชม.)", DataType.String, "body");
            wrRow.Cells.Add("จำนวนเวลาปฏิบัติงานล่วงเวลา (ชม.)", DataType.String, "body");
            wrCell = wrRow.Cells.Add("รายละเอียดของงานที่ทำ", DataType.String, "body");
            wrCell.MergeAcross = 2;
            wrRow.Height = 40;
        }

        private void BOT_WriteFooter(Worksheet wsSheet, WorksheetRow wrRow, WorksheetCell wrCell, string StaffName, string ProjectName, int sumRecord)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrCell = wrRow.Cells.Add("รวมทั้งสิ้น", DataType.String, "header");
            wrCell.MergeAcross = 1;

            var sumcell = wrRow.Cells.Add();
            sumcell.Formula = "=SUM(R[-" + sumRecord + "]C:R[-1]C)";
            sumcell.StyleID = "header";

            wrRow = wsSheet.Table.Rows.Add();
            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("", DataType.String, "bodynoborder");
            wrCell = wrRow.Cells.Add("ลงชื่อ .............................................", DataType.String, "bodynoborder");
            wrCell.MergeAcross = 1;
            wrRow.Cells.Add("", DataType.String, "bodynoborder");
            wrRow.Cells.Add("", DataType.String, "bodynoborder");
            wrRow.Cells.Add("ลงชื่อ .............................................", DataType.String, "bodynoborder");
            wrRow.Height = 33;

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("(", DataType.String, "bodynoborderright");
            wrCell = wrRow.Cells.Add(ProjectName, DataType.String, "bodynoborder");
            wrCell.MergeAcross = 1;
            wrRow.Cells.Add(")", DataType.String, "bodynoborderleft");
            wrRow.Cells.Add("(", DataType.String, "bodynoborderright");
            wrRow.Cells.Add(StaffName, DataType.String, "bodynoborder");
            wrRow.Cells.Add(")", DataType.String, "bodynoborderleft");

            wrRow = wsSheet.Table.Rows.Add();
            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("", DataType.String, "bodynoborder");
            wrCell = wrRow.Cells.Add("ธปท.", DataType.String, "bodynoborder");
            wrCell.MergeAcross = 1;
            wrRow.Cells.Add("", DataType.String, "bodynoborder");
            wrRow.Cells.Add("", DataType.String, "bodynoborder");
            wrRow.Cells.Add("บ.คิวบ์ออฟไนน์ จำกัด", DataType.String, "bodynoborder");

        }

        private void BOT_MainColumnConfiguration(Worksheet wsSheet)
        {
            wsSheet.Table.Columns.Add(new WorksheetColumn(80));
            wsSheet.Table.Columns.Add(new WorksheetColumn(110));
            wsSheet.Table.Columns.Add(new WorksheetColumn(100));
            wsSheet.Table.Columns.Add(new WorksheetColumn(100));
            wsSheet.Table.Columns.Add(new WorksheetColumn(60));
            wsSheet.Table.Columns.Add(new WorksheetColumn(180));
            wsSheet.Table.Columns.Add(new WorksheetColumn(50));
        }

        private void BOT_SumColumnConfiguration(Worksheet sumSheet)
        {
            sumSheet.Table.Columns.Add(new WorksheetColumn(70));
            sumSheet.Table.Columns.Add(new WorksheetColumn(180));
            sumSheet.Table.Columns.Add(new WorksheetColumn(80));
            sumSheet.Table.Columns.Add(new WorksheetColumn(100));
            sumSheet.Table.Columns.Add(new WorksheetColumn(100));
            sumSheet.Table.Columns.Add(new WorksheetColumn(150));
        }

        private void BOT_AddEmtypyCell(int cellNo, WorksheetRow wrRow, string Style)
        {
            for (int i = 0; i < cellNo; i++)
            {
                if (Style == string.Empty)
                {
                    wrRow.Cells.Add("");

                }
                else
                {
                    wrRow.Cells.Add("", DataType.String, Style);
                }

            }
        }

        #endregion

        #region "Cube Configuration"

        #region " Column Configuration "

        private void HeaderConfiguration(Worksheet wsSheet, WorksheetRow wrRow, WorksheetCell wrCell)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrCell = wrRow.Cells.Add("CUBE OF NINE'S TIMESHEET", DataType.String, "header");
            wrCell.MergeAcross = 11;
            wrRow.Height = 30;

            ColumnConfiguration(wsSheet, wrRow);

        }

        private void StyleConfiguration(Workbook book)
        {

            ExcelStyle.Header(book);
            ExcelStyle.Grey_Cen_Top_Botton(book);
            ExcelStyle.Grey_Right_NoBoder(book);
            ExcelStyle.Grey_Cen_Bold_Top_Botton(book);
            ExcelStyle.Grey_Right_Bold_Top_Botton(book);
            ExcelStyle.Header_Center_Border(book);
            ExcelStyle.Header_Left_NoBorder(book);
            ExcelStyle.Header_Left_Border(book);
            ExcelStyle.Header_Right_NoBorder(book);
            ExcelStyle.Header_FontNotBold_NoBorder(book);
            ExcelStyle.Body_Right_BorderDash(book);
            ExcelStyle.Body_Left_BorderDash(book);
            ExcelStyle.Body_Cen_BorderDash(book);
            ExcelStyle.Body_Right_BorderTop(book);
            ExcelStyle.Body_Right_BorderBottom(book);
            ExcelStyle.Body_Left_BorderTop(book);
            ExcelStyle.Body_Left_BorderBottom(book);
            ExcelStyle.Body_Center_Border(book);
            ExcelStyle.Body_Left_Border(book);
            ExcelStyle.Body_Left_NoBorder(book);
            ExcelStyle.Body_Right_NoBorder(book);
            ExcelStyle.Body_Center_NoBorder(book);
            ExcelStyle.Footer_Right_Bold_Border(book);
            ExcelStyle.Footer_Left_Bold_NoBorder(book);
            ExcelStyle.Footer_bottomBorder(book);
        }

        private void BookConfiguration(Workbook book)
        {
            book.ExcelWorkbook.ActiveSheetIndex = 0;
            book.ExcelWorkbook.WindowTopX = 100;
            book.ExcelWorkbook.WindowTopY = 200;
            book.ExcelWorkbook.WindowHeight = 7000;
            book.ExcelWorkbook.WindowWidth = 8000;
            book.Properties.Author = "Administrator";
            book.Properties.Title = "Excel Export";
            book.Properties.Created = DateTime.Now;
        }

        private void ColumnConfiguration(Worksheet wsSheet, WorksheetRow wrRow)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wsSheet.Table.Columns.Add(new WorksheetColumn(75));
            wsSheet.Table.Columns.Add(new WorksheetColumn(40));
            wsSheet.Table.Columns.Add(new WorksheetColumn(120));
            wsSheet.Table.Columns.Add(new WorksheetColumn(55));
            wsSheet.Table.Columns.Add(new WorksheetColumn(55));
            wsSheet.Table.Columns.Add(new WorksheetColumn(55));
            wsSheet.Table.Columns.Add(new WorksheetColumn(55));
            wsSheet.Table.Columns.Add(new WorksheetColumn(55));
            wsSheet.Table.Columns.Add(new WorksheetColumn(55));
            wsSheet.Table.Columns.Add(new WorksheetColumn(55));
            wsSheet.Table.Columns.Add(new WorksheetColumn(55));
            wsSheet.Table.Columns.Add(new WorksheetColumn(150));
        }

        #endregion

        #region " Header "

        private void ImageLogo(Workbook book, WorksheetRow wrRow)
        {
            string filename = MapPath("../images/TS_Logo.jpg");
            Bitmap image = new Bitmap(filename);
            BitmapToArray bta = new BitmapToArray(image);
            var arr = bta.GetArray();
            ExcelStyle.ImageStyles(arr, book);

            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("", DataType.String, "headerleft");
            wrRow.Cells.Add("", DataType.String, "headerleft");

            wrRow.Cells.Add(ExcelStyle.GetCell(arr));

        }

        #endregion

        #region " Footer Leave"

        private void getOnLeaveDetailFooter(Worksheet wsSheet, WorksheetRow wrRow, List<OLTS.MODEL.OTS_TIME_SHEET> detail)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("On Leave", DataType.String, "Body_Left_BorderTop");
            wrRow.Cells.Add("OL", DataType.String, "Body_Left_BorderTop");
            wrRow.Cells.Add("", DataType.String, "Body_Left_BorderTop");
            var sumcell = wrRow.Cells.Add();
            sumcell.Formula = "=SUM(RC[7]:RC[1])";
            sumcell.StyleID = "Body_Right_BorderTop";
            wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");
            if (isLeave(detail, 1))
            {
                int row = 0;
                foreach (var sheet in detail)
                {
                    row = row + 1;
                    if (row == 1)
                    {
                        var obj = sheet.OTS_WORK_DETAIL.Where(x => x.LEAVE_ID == 1).ToList();

                        if (obj.Count > 0)
                        {
                            var leave = obj.First();
                            wrRow.Cells.Add(leave.AMOUNT_OF_LEAVE.ToString(), DataType.Number, "Body_Right_BorderTop");
                        }
                        else
                        {
                            addEmptyCellAfter(wrRow, row);
                        }
                    }
                }
                wrRow.Cells.Add("", DataType.String, "Footer_Right_Bold_Border");
                wrRow.Cells.Add("", DataType.String, "Footer_Right_Bold_Border");
            }
            else
            {
                addEmptyCellBeforeNoBG(wrRow, 7);
            }
        }

        private void getAnnualHolidayDetailFooter(Worksheet wsSheet, WorksheetRow wrRow, List<OLTS.MODEL.OTS_TIME_SHEET> detail)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("Annual Holiday", DataType.String, "Body_Left_BorderDash");
            wrRow.Cells.Add("AH", DataType.String, "Body_Left_BorderDash");
            wrRow.Cells.Add("", DataType.String, "Body_Left_BorderDash");
            var sumcell = wrRow.Cells.Add();
            sumcell.Formula = "=SUM(RC[7]:RC[1])";
            sumcell.StyleID = "Body_Cen_BorderDash";
            wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");

            if (isLeave(detail, 2))
            {
                int row = 0;
                foreach (var sheet in detail)
                {
                    row = row + 1;
                    if (row == 1)
                    {
                        var obj = sheet.OTS_WORK_DETAIL.Where(x => x.LEAVE_ID == 2).ToList();

                        if (obj.Count > 0)
                        {
                            var leave = obj.First();
                            wrRow.Cells.Add(leave.AMOUNT_OF_LEAVE.ToString(), DataType.Number, "Body_Left_BorderDash");
                        }
                        else
                        {
                            addEmptyCellAfter(wrRow, row);
                        }
                    }

                }
                wrRow.Cells.Add("", DataType.String, "Body_Left_BorderDash");
                wrRow.Cells.Add("", DataType.String, "Body_Left_BorderDash");
            }
            else
            {
                addEmptyCellBeforeNoBG(wrRow, 7);
            }

        }

        private void getPublicHolidayDetailFooter(Worksheet wsSheet, WorksheetRow wrRow, List<OLTS.MODEL.OTS_TIME_SHEET> detail)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("Public Holiday", DataType.String, "Body_Left_BorderBottom");
            wrRow.Cells.Add("PH", DataType.String, "Body_Left_BorderBottom");
            wrRow.Cells.Add("", DataType.String, "Body_Left_BorderBottom");
            var sumcell = wrRow.Cells.Add();
            sumcell.Formula = "=SUM(RC[7]:RC[1])";
            sumcell.StyleID = "Body_Right_BorderBottom";

            wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");

            if (isLeave(detail, 3))
            {
                int row = 0;
                foreach (var sheet in detail)
                {
                    row = row + 1;
                    if (row == 1)
                    {
                        var obj = sheet.OTS_WORK_DETAIL.Where(x => x.LEAVE_ID == 3).ToList();

                        if (obj.Count > 0)
                        {
                            var leave = obj.First();
                            wrRow.Cells.Add(leave.AMOUNT_OF_LEAVE.ToString(), DataType.Number, "Body_Right_BorderBottom");
                        }
                        else
                        {
                            addEmptyCellAfter(wrRow, row);
                        }
                    }

                }
                wrRow.Cells.Add("", DataType.String, "Footer_Right_Bold_Border");
                wrRow.Cells.Add("", DataType.String, "Footer_Right_Bold_Border");
            }
            else
            {
                addEmptyCellBeforeNoBG(wrRow, 7);
            }

        }

        private bool isLeave(List<OLTS.MODEL.OTS_TIME_SHEET> sheet, int type)
        {
            var result = false;
            foreach (var o in sheet)
            {
                var obj = o.OTS_WORK_DETAIL.Where(x => x.LEAVE_ID == type).ToList();

                if (obj.Count > 0)
                {
                    result = true;
                }
            }

            return result;
        }

        #endregion

        #region "Footer"

        private void getSignature(Worksheet wsSheet, WorksheetRow wrRow, List<OLTS.MODEL.OTS_TIME_SHEET> detail, WorksheetCell wrCell, string StaffName, string ApproveName, string ApproveNameLast)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("COMPLETE BY:", DataType.String, "Footer_Left_Bold_NoBorder");
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "header");
            wrCell = wrRow.Cells.Add("APPROVED BY:", DataType.String, "Footer_Left_Bold_NoBorder");
            wrCell.MergeAcross = 1;
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "header");
            wrCell = wrRow.Cells.Add("APPROVED BY:", DataType.String, "Footer_Left_Bold_NoBorder");
            wrCell.MergeAcross = 1;

            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("", DataType.String, "Footer_bottomBorder");
            wrRow.Cells.Add("", DataType.String, "Footer_bottomBorder");
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "Footer_bottomBorder");
            wrRow.Cells.Add("", DataType.String, "Footer_bottomBorder");
            wrRow.Cells.Add("", DataType.String, "Footer_bottomBorder");
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "Footer_bottomBorder");
            wrRow.Cells.Add("", DataType.String, "Footer_bottomBorder");
            wrRow.Cells.Add("", DataType.String, "Footer_bottomBorder");

            wrRow = wsSheet.Table.Rows.Add();
            wrCell = wrRow.Cells.Add(StaffName, DataType.String, "bodynoborderleft");
            wrCell.MergeAcross = 1;
            wrRow.Cells.Add("", DataType.String, "header");
            wrCell = wrRow.Cells.Add(ApproveName, DataType.String, "bodynoborderleft");
            wrCell.MergeAcross = 2;
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "header");
            wrCell = wrRow.Cells.Add(ApproveNameLast, DataType.String, "bodynoborderleft");
            wrCell.MergeAcross = 3;

        }

        private void getGrandTotal(Worksheet wsSheet, WorksheetRow wrRow, List<OLTS.MODEL.OTS_TIME_SHEET> detail)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("Grand Total", DataType.String, "Footer_Right_Bold_Border");
            for (int i = 0; i < 8; i++)
            {
                if (i == 1 || i == 7)
                {
                    var sumcell = wrRow.Cells.Add();
                    sumcell.Formula = "=SUM(R[-5]C,R[-1]C)";
                    sumcell.StyleID = "Grey_Right_Bold_Top_Botton";
                }
                else
                {
                    var sumcell = wrRow.Cells.Add();
                    sumcell.Formula = "=SUM(R[-5]C,R[-1]C)";
                    sumcell.StyleID = "Footer_Right_Bold_Border";
                }

            }
            wrRow.Cells.Add("", DataType.String, "Footer_Right_Bold_Border");
        }

        private void getTotalOfLeave(Worksheet wsSheet, WorksheetRow wrRow, List<OLTS.MODEL.OTS_TIME_SHEET> detail)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("Total of Leave", DataType.String, "Footer_Right_Bold_Border");

            for (int i = 0; i < 8; i++)
            {
                if (i == 1 || i == 7)
                {
                    var sumcell = wrRow.Cells.Add();
                    sumcell.Formula = "=SUM(R[-3]C:R[-1]C)";
                    sumcell.StyleID = "Grey_Right_Bold_Top_Botton";
                }
                else
                {
                    var sumcell = wrRow.Cells.Add();
                    sumcell.Formula = "=SUM(R[-3]C:R[-1]C)";
                    sumcell.StyleID = "Footer_Right_Bold_Border";
                }
            }
            wrRow.Cells.Add("", DataType.String, "Footer_Right_Bold_Border");

        }

        private void getTotalofProject(Worksheet wsSheet, WorksheetRow wrRow, List<OLTS.MODEL.OTS_TIME_SHEET> detail)
        {
            wrRow = wsSheet.Table.Rows.Add();
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("", DataType.String, "header");
            wrRow.Cells.Add("Total of Project", DataType.String, "Footer_Right_Bold_Border");
            var sumTotalcell = wrRow.Cells.Add();
            sumTotalcell.Formula = "=SUM(R[-" + detail.Count + "]C:R[-1]C)";
            sumTotalcell.StyleID = "Footer_Right_Bold_Border";

            var sumSuncell = wrRow.Cells.Add();
            sumSuncell.Formula = "=SUM(R[-" + detail.Count + "]C:R[-1]C)";
            sumSuncell.StyleID = "Grey_Right_NoBoder";
            var sumMoncell = wrRow.Cells.Add();
            sumMoncell.Formula = "=SUM(R[-" + detail.Count + "]C:R[-1]C)";
            sumMoncell.StyleID = "Footer_Right_Bold_Border";
            var sumTuecell = wrRow.Cells.Add();
            sumTuecell.Formula = "=SUM(R[-" + detail.Count + "]C:R[-1]C)";
            sumTuecell.StyleID = "Footer_Right_Bold_Border";
            var sumWedcell = wrRow.Cells.Add();
            sumWedcell.Formula = "=SUM(R[-" + detail.Count + "]C:R[-1]C)";
            sumWedcell.StyleID = "Footer_Right_Bold_Border";
            var sumThucell = wrRow.Cells.Add();
            sumThucell.Formula = "=SUM(R[-" + detail.Count + "]C:R[-1]C)";
            sumThucell.StyleID = "Footer_Right_Bold_Border";
            var sumFricell = wrRow.Cells.Add();
            sumFricell.Formula = "=SUM(R[-" + detail.Count + "]C:R[-1]C)";
            sumFricell.StyleID = "Footer_Right_Bold_Border";
            var sumSatcell = wrRow.Cells.Add();
            sumSatcell.Formula = "=SUM(R[-" + detail.Count + "]C:R[-1]C)";
            sumSatcell.StyleID = "Grey_Right_NoBoder";

            wrRow.Cells.Add("", DataType.String, "Footer_Right_Bold_Border");

        }

        #endregion

        #endregion

        private void addEmptyCellBefore(WorksheetRow wrRow, int cell)
        {
            for (int i = 0; i < cell; i++)
            {
                if (i == 0)
                {
                    wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");
                }
                else
                {
                    wrRow.Cells.Add("", DataType.String, "Body_Right_BorderDash");
                }
            }

        }

        private void addEmptyCellBeforeNoBG(WorksheetRow wrRow, int cell)
        {
            for (int i = 0; i < cell; i++)
            {
                if (i == cell - 2)
                {
                    wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");

                }
                else
                {
                    wrRow.Cells.Add("", DataType.String, "Body_Right_BorderDash");
                }
            }

        }

        private void addEmptyCellAfter(WorksheetRow wrRow, int cell)
        {
            var loop = 7 - cell;
            for (int i = 0; i < loop; i++)
            {
                if (i == loop - 2)
                {
                    wrRow.Cells.Add("", DataType.String, "Grey_Cen_Top_Botton");
                }
                else
                {
                    wrRow.Cells.Add("", DataType.String, "Body_Right_BorderDash");
                }
            }

        }

        private string getJobCode(List<OTS_WORK_DETAIL> detail)
        {
            var result = "";
            foreach (var item in detail)
            {
                result += item.JOB_CODE + "\r\n";
            }
            return result;
        }

        private string getDescription(List<OTS_WORK_DETAIL> detail)
        {
            var result = "";
            foreach (var item in detail)
            {
                result += item.DETAIL + "\r\n";
            }
            return result;
        }

        private string getRemark(List<OTS_WORK_DETAIL> detail)
        {
            var result = "";
            foreach (var item in detail)
            {
                result += item.REMARK + "\r\n";
            }
            return result;
        }

        private string getAmonthTotal(List<OTS_WORK_DETAIL> detail)
        {
            var result = 0;
            foreach (var item in detail)
            {
                result = result + Utility.CheckInt(item.AMOUNT_OF_TIME);
            }
            if (result > 0)
            {
                return result.ToString();
            }
            else
            {
                return "";
            }
        }

        private string getLeaveTypeID(List<OTS_WORK_DETAIL> detail)
        {
            var result = "";
            foreach (var item in detail)
            {
                if (item.LEAVE_ID != null)
                {
                    result = item.LEAVE_ID.ToString();
                }
            }
            return result.ToString();

        }

        private string getAmontOfLeave(List<OTS_WORK_DETAIL> detail)
        { 
            var result = 0;
            foreach (var item in detail)
            {
                if (item.LEAVE_ID != null)
                {
                    result = Utility.CheckInt(item.AMOUNT_OF_LEAVE);
                }
            }

            if (result > 0)
            {
                return result.ToString();
            }
            else
            {
                return "";
            }
        }

        private void LoadBot(List<OLTS.MODEL.OTS_TIME_SHEET> detail, Emp_On_Proj_Detail site, string WeekValue)
        {
            string[] strWeek = WeekValue.Split(',');
            detail = detail.Where(x => Convert.ToDateTime(x.DATE).Month == Convert.ToInt16(strWeek[0]) && (Convert.ToDateTime(x.DATE).Day >= Convert.ToInt16(strWeek[1]) && Convert.ToDateTime(x.DATE).Day <= Convert.ToInt16(strWeek[2]))).ToList();
            string str = "<table id='tbl' CELLPADDING='0' CELLSPACING='0' runat='server' >";
            str += "<tr> <td></td> <td  ></td> <td ></td> <td></td> <td></td><td></td><td></td><td></td> </tr> ";
            str += "<tr> <td></td> <td></td> <td colspan='4' style='text-align:center; font-weight: bold;' > รายงานการปฏิบัติงาน รายบุคคล </td> <td></td><td></td><td></td> </tr> ";
            if (detail.Count > 0)
            {
                str += "<tr> <td></td> <td></td> <td style='text-align:right; font-weight: bold;'>ประจำวันที่</td> <td colspan='2' style='text-align:center; font-weight: bold;'> " + getFullMonthDate(Convert.ToDateTime(detail[0].DATE)) + " ถึง  " + getFullMonthDate(Convert.ToDateTime(detail[detail.Count - 1].DATE)) + "</td><td></td><td></td><td></td> </tr> ";
            }
            else
            {
                str += "<tr> <td></td> <td></td> <td style='text-align:right;'>ประจำวันที่</td> <td style='text-align:center;'>  </td> <td>ถึง</td><td style='text-align:left;'> </td><td></td><td></td> </tr> ";

            }
            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td></td><td></td> <td></td> </tr> ";
            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td style='text-align:right; font-weight: bold;' >รายงานวันที่</td><td>" + getFullMonthDate(DateTime.Now) + "</td> <td></td> </tr> ";
            str += "<tr> <td style='text-align:right; font-weight: bold;' >ชื่อ - นามสกุล</td> <td style='text-align:left;' >" + site.STAFF_NAME_TH + "</td> <td></td> <td  style='text-align:right; font-weight: bold;' >ประเภทงาน</td> <td colspan='4' style='text-align:left;'>" + site.PROJECT_NAME + "</td> </tr> ";
            str += "<tr><td colspan='8'> <table id='tbl1'   CELLPADDING='0' CELLSPACING='0' runat='server' > ";
            str += "<tr  > <td style='border: 1px solid black; text-align:center;'>วันที่</td> <td width='50px' style='border: 1px solid black; text-align:center;'>เวลาปฏิบัติงาน(เริ่ม – เลิก)</td> <td width='50px' style='border: 1px solid black; text-align:center;'>จำนวนเวลาปฏิบัติงานปกติ  (ชม.)</td> <td style='border: 1px solid black; text-align:center;'>จำนวนเวลาปฏิบัติงานล่วงเวลา (ชม.)</td> <td colspan='4' style='border: 1px solid black; text-align:center; '>รายละเอียดของงานที่ทำ</td>  </tr> ";

            decimal AllHour = 0;

            foreach (var sheet in detail.OrderBy(x => x.DATE))
            {
                decimal WorkHour = 0;
                decimal WorkOt = 0;
                string Workdesc = "";
                var workingdate = getWorkingDate(Convert.ToDateTime(sheet.DATE));

                str += "<tr  > <td style='border: 1px solid black; text-align:center; '>" + workingdate + "</td> <td style='border: 1px solid black; text-align:center;'>" + getWorkingTime(sheet.OTS_WORK_DETAIL.ToList()) + "</td>  ";
                foreach (var detial in sheet.OTS_WORK_DETAIL)
                {
                    AllHour += Convert.ToDecimal(detial.AMOUNT_OF_TIME);
                    WorkHour += Convert.ToDecimal(detial.AMOUNT_OF_TIME);
                    WorkOt += Convert.ToDecimal(detial.AMOUNT_OF_TIME_OVER_TIME);
                    Workdesc += "</br>" + detial.DETAIL;
                }
                str += "  <td style='border: 1px solid black; text-align:center;'>" + WorkHour + "</td> <td style='border: 1px solid black; text-align:center;'>" + WorkOt + "</td> <td colspan='4'  style='border: 1px solid black'>" + Workdesc + "</td>  ";
                str += " </tr> ";
            }
            str += "<tr  > <td colspan='2' style='text-align:center; border: 1px solid black' >รวมทั้งสิ้น</td> <td style='border: 1px solid black;  text-align:center; '>" + AllHour + "</td>    </tr> ";
            str += " </table> </td> </tr>";

            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td></td><td></td> </tr> ";
            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td></td><td></td> </tr> ";
            str += "<tr> <td colspan='4'style='text-align:center;' >ลงชื่อ ...............................................</td> <td colspan='4' style='text-align:center;' >ลงชื่อ ...............................................</td>  </tr> ";
            str += "<tr>   <td colspan='4' style='text-align:center; text-decoration: underline; ' >(    " + site.PROJECT_OWN + "   )</td>   <td colspan='4' style='text-align:center;  text-decoration: underline;  ' >(    " + site.STAFF_NAME_TH + "    )</td> </tr> ";
            str += "<tr>   <td  colspan='4'style='text-align:center;'>ผู้บริหารทีม</td> </tr> ";
            str += " </table>";
            //exportNew(str);
            //Export_file(str, "BOT_" + site.STAFF_NAME_EN);

        }

        private string getWorkingTime(List<OTS_WORK_DETAIL> detail)
        {
            var result = "";
            if (detail.Count() > 0)
            {
                result = detail.First().START_TIME + "-" + detail.Last().END_TIME;
            }

            return result;
        }

        private void LoadBotSum(List<OLTS.MODEL.OTS_TIME_SHEET> detail, Emp_On_Proj_Detail site, string WeekValue)
        {
            string[] strWeek = WeekValue.Split(',');

            string str = "<table id='tbl' CELLPADDING='0' CELLSPACING='0' runat='server' >";
            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td></td><td></td> </tr> ";
            str += "<tr> <td></td> <td></td> <td colspan='4' style='text-align:center;  font-weight: bold;' > รายงานสรุปผลงาน </td> <td></td><td></td><td></td> </tr> ";
            str += "<tr> <td></td> <td></td> <td colspan='4' style='text-align:center;  font-weight: bold;' > บริษัท คิวบ์ออฟไนน์ จำกัด </td> <td></td><td></td><td></td> </tr> ";
            if (detail.Count > 0)
            {
                str += "<tr> <td></td> <td></td> <td style='text-align:right; font-weight: bold;'>ประจำวันที่</td> <td colspan='2' style='text-align:center; font-weight: bold;'> " + getFullMonthDate(Convert.ToDateTime(detail[0].DATE)) + " ถึง  " + getFullMonthDate(Convert.ToDateTime(detail[detail.Count - 1].DATE)) + "</td><td></td><td></td><td></td> </tr> ";
            }
            else
            {
                str += "<tr> <td></td> <td></td> <td style='text-align:right;'>ประจำวันที่</td> <td style='text-align:center;'>  </td> <td>ถึง</td><td style='text-align:left;'> </td><td></td><td></td> </tr> ";

            }
            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td></td><td></td> <td></td> </tr> ";
            str += "<tr> <td></td> <td></td> <td></td> <td></td> <td></td><td></td><td>รายงานวันที่</td><td>" + getFullMonthDate(DateTime.Now) + "</td> <td></td> </tr> ";
            str += "<tr> <td>ชื่อ - นามสกุล</td> <td>" + site.STAFF_NAME_TH + "</td> <td></td> <td>ประเภทงาน</td> <td colspan='4'>" + site.PROJECT_NAME + "</td> </tr> ";
            str += "<tr > <td style='text-align:center; border: 1px solid black;  ' >ลำดับที่</td> <td colspan='2' style='text-align:center; border: 1px solid black;  '> ขั้นตอนของงาน</td> <td style='text-align:center; border: 1px solid black;  '>งานที่ส่งมอบ</td> <td style='text-align:center; border: 1px solid black;  '>สถานะของงาน</td> <td style='text-align:center; border: 1px solid black;  '>วันที่แล้วเสร็จ</td><td colspan='2' style='text-align:center; border: 1px solid black;  '>หมายเหตุ</td>  </tr> ";
            int i = 1;
            foreach (OLTS.MODEL.OTS_TIME_SHEET sheet in detail)
            {
                if (Convert.ToDateTime(sheet.DATE).Month.ToString() != strWeek[0] && Convert.ToDateTime(sheet.DATE).Day < Convert.ToInt16(strWeek[1]) && Convert.ToDateTime(sheet.DATE).Day > Convert.ToInt16(strWeek[0]))
                {
                    continue;
                }
                foreach (OLTS.MODEL.OTS_WORK_DETAIL item in sheet.OTS_WORK_DETAIL)
                {
                    str += "<tr > <td style='text-align:center; border: 1px solid black;  ' >" + i + "</td> <td colspan='2' style='text-align:center; border: 1px solid black;  '>" + item.DETAIL + "</td> <td style='text-align:center; border: 1px solid black;  '>" + item.DETAIL + "</td> <td style='text-align:center; border: 1px solid black;  '>" + site.JOB_STATUS + "</td> <td style='text-align:center; border: 1px solid black;  '>" + getFullMonthDate(Convert.ToDateTime(sheet.DATE)) + "</td><td colspan='2' style='text-align:center; border: 1px solid black;  '>" + item.REMARK + "</td>  </tr> ";
                    i++;
                }

            }
            str += " </table>";
            Export_file(str, "BOT_SUM_" + site.STAFF_NAME_EN);
            Response.End();

        }

        private void LoadOther(List<OLTS.MODEL.OTS_TIME_SHEET> sheets, Emp_On_Proj_Detail site, string WeekValue)
        {
            string[] temp = WeekValue.Split(',');
            year = Convert.ToInt16(ddlYear.SelectedValue);
            month = Convert.ToInt16(ddlMonth.SelectedValue);
            string dateFrm = getFullMonthDate(year, month, Convert.ToInt16(temp[1]));
            string dateTo = getFullMonthDate(year, month, Convert.ToInt16(temp[2]));

            string strImage = HttpContext.Current.Request.Url.ToString().Replace(HttpContext.Current.Request.CurrentExecutionFilePath, "") + "/images/Co9_logo.jpg";

            string str = "<table id='tbl' border='0' CELLPADDING='0' CELLSPACING='0'  runat='server' >";
            str += "<tr><td></td><td></td><td></td><td></td><td></td><td></td>  <td></td><td></td><td></td><td></td><td></td><td></td> </tr> ";
            str += "<tr>  <td colspan='12' style='text-align:center; font-weight: bold; font-size:18px;' > CUBE OF NINE'S TIMESHEET </td>   </tr> ";

            str += "<tr> <td style='text-align:center; font-weight: bold;'>Staff Name</td> <td colspan='2' style='text-align:left;' >" + site.STAFF_NAME_TH + "</td>  <td></td> <td style='text-align:center; font-weight: bold;'>Client</td><td colspan='2'  >" + site.SITE_NAME + "</td> <td></td><td></td> <td></td> <td></td> <td></td></tr> ";
            str += "<tr> <td style='text-align:center; font-weight: bold;'>Staff ID</td> <td colspan='2' style='text-align:left;' >" + site.STAFF_ID + "       </td>  <td></td> <td>      </td><td colspan='2'>                      </td>  <td></td> <td></td><td></td><td></td>  <td rowspan='3' style='width:230px;' > <table id='tbl_pic' border='0' CELLPADDING='0' CELLSPACING='0'  runat='server' > <tr> <td style='width:10px;'></td>  <td>  <img alt='a'    src='" + strImage + "' />  </td> <td style='width:200px;'> </td> </tr> </table> </td> </tr> ";
            str += "<tr> <td style='text-align:center; font-weight: bold;'>Position</td> <td colspan='2' style='text-align:left;' >" + site.STAFF_POSITION + "</td>  <td></td> <td style='text-align:center; font-weight: bold;'>Week of</td><td colspan='2'>" + dateFrm + "</td><td style='text-align:center; font-weight: bold;'>TO</td><td>" + dateTo + "</td><td></td>  <td></td></tr> ";

            str += "<tr> <td colspan='3' rowspan='2' style='text-align:center; border: 1px solid black; font-weight: bold; ' > Weekly <br/> Time Sheet</td>  <td style='text-align:center; border: 1px solid black; font-weight: bold; '>Time In</td> <td style='border: 1px solid black; background-color: gray;'></td> ";

            int j = 5;
            int invalidMonth = 0;
            if (sheets.Count > 0)
            {
                for (int z = 0; z < (5 - sheets.ToList().Count); z++)
                {
                    for (int i = 0; i < 5; i++)
                    {
                        if (Convert.ToInt32(temp[2]) - i == Convert.ToDateTime(sheets[z].DATE).Day)
                        {
                            str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                            invalidMonth++;
                        }
                    }

                    j--;
                }
            }

            for (int i = 0; i < sheets.ToList().Count; i++)
            {
                for (int k = 0; k < j; k++)
                {
                    if (Convert.ToInt32(temp[2]) - k == Convert.ToDateTime(sheets[i].DATE).Day)
                    {
                        string[] strTemp = sheets[i].TIME.Split('-');
                        if (strTemp.Length > 0)
                        {
                            str += " <td style='border: 1px solid black;text-align:center;'>" + strTemp[0].ToString() + "</td>";
                            invalidMonth++;
                        }
                        continue;
                    }
                }
            }
            if (invalidMonth == 0)
            {
                for (int i = 0; i < 5; i++)
                {
                    str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                }
            }
            str += "<td style='border: 1px solid black; background-color: gray;'></td>   </tr> ";
            str += "<tr>                                                                                                               <td style='text-align:center; border: 1px solid black; font-weight: bold; '>Time Out</td> <td style='border: 1px solid black; background-color: gray;'></td>  ";
            j = 5;
            invalidMonth = 0;
            if (sheets.Count > 0)
            {
                for (int z = 0; z < (5 - sheets.ToList().Count); z++)
                {
                    for (int i = 0; i < 5; i++)
                    {
                        if (Convert.ToInt32(temp[2]) - i == Convert.ToDateTime(sheets[z].DATE).Day)
                        {
                            str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                            invalidMonth++;
                        }
                    }
                    j--;
                }
            }
            if (sheets.Count > 0)
            {
                for (int i = 0; i < sheets.ToList().Count; i++)
                {
                    for (int k = 0; k < j; k++)
                    {
                        if (Convert.ToInt32(temp[2]) - k == Convert.ToDateTime(sheets[i].DATE).Day)
                        {
                            string[] strTemp = sheets[i].TIME.Split('-');
                            if (strTemp.Length > 1)
                            {
                                str += " <td style='border: 1px solid black; text-align:center;'>" + strTemp[1].ToString() + "</td>";
                                invalidMonth++;
                            }
                            continue;
                        }
                    }
                }
            }
            if (invalidMonth == 0)
            {
                for (int i = 0; i < 5; i++)
                {
                    str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                }
            }
            str += "<td style='border: 1px solid black; background-color: gray;'></td>  </tr> ";
            str += "<tr> <td rowspan='2' style='text-align:center; border: 1px solid black; vertical-align:top;' >Project</td> <td rowspan='2' style='text-align:center; border: 1px solid black'> Job <br/> Code</td> <td rowspan ='2' style='text-align:center;vertical-align:top; border: 1px solid black'>Description</td> <td style='text-align:center; border: 1px solid black'>Total <br/> Hours</td> <td style='text-align:center; background-color: gray; border: 1px solid black'>Sun</td><td style='text-align:center; border: 1px solid black'>Mon</td><td style='text-align:center; border: 1px solid black'>Tue</td><td style='text-align:center; border: 1px solid black'>Wed</td><td style='text-align:center; border: 1px solid black'>Thu</td><td style='text-align:center; border: 1px solid black'>Fri</td><td style='text-align:center; background-color: gray; border: 1px solid black'>Sat</td><td rowspan='2' style='border: 1px solid black;'>Remark</td> </tr> ";


            str += "<tr> ";
            str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
            if ((Convert.ToInt16(temp[2]) - Convert.ToInt16(temp[1])) >= 4)
            {
                str += "  <td style='text-align:center; border: 1px solid black; background-color: gray;'>" + getFullMonthDate(year, month, Convert.ToInt16(temp[2]) - 5) + "</td> ";
            }
            else
            {
                str += "  <td style='text-align:center; border: 1px solid black; background-color: gray;'> </td> ";
            }
            if ((Convert.ToInt16(temp[2]) - Convert.ToInt16(temp[1])) >= 4)
            {
                str += "  <td style='text-align:center; border: 1px solid black'>" + getFullMonthDate(year, month, Convert.ToInt16(temp[2]) - 4) + "</td> ";
            }
            else
            {
                str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
            }
            if ((Convert.ToInt16(temp[2]) - Convert.ToInt16(temp[1])) >= 3)
            {
                str += "  <td style='text-align:center; border: 1px solid black'>" + getFullMonthDate(year, month, Convert.ToInt16(temp[2]) - 3) + "</td> ";
            }
            else
            {
                str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
            }

            if ((Convert.ToInt16(temp[2]) - Convert.ToInt16(temp[1])) >= 2)
            {
                str += "  <td style='text-align:center; border: 1px solid black'>" + getFullMonthDate(year, month, Convert.ToInt16(temp[2]) - 2) + "</td> ";
            }
            else
            {
                str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
            }
            if ((Convert.ToInt16(temp[2]) - Convert.ToInt16(temp[1])) >= 1)
            {
                str += "  <td style='text-align:center; border: 1px solid black'>" + getFullMonthDate(year, month, Convert.ToInt16(temp[2]) - 1) + "</td> ";
            }
            else
            {
                str += "  <td style='text-align:center; border: 1px solid black;'> </td> ";
            }
            str += "  <td style='text-align:center; border: 1px solid black;  '>" + getFullMonthDate(year, month, Convert.ToInt16(temp[2])) + "</td> ";
            str += "   <td style='text-align:center; border: 1px solid black; background-color: gray;'>" + getFullMonthDate(year, month, Convert.ToInt16(temp[2]) + 1) + "</td>    </tr> ";
            Decimal SumAllTime = 0;
            Decimal sumDay1 = 0;
            Decimal sumDay2 = 0;
            Decimal sumDay3 = 0;
            Decimal sumDay4 = 0;
            Decimal sumDay5 = 0;
            Decimal LeaveAllTime = 0;
            Decimal LeaveDay1 = 0;
            Decimal LeaveDay2 = 0;
            Decimal LeaveDay3 = 0;
            Decimal LeaveDay4 = 0;
            Decimal LeaveDay5 = 0;
            Decimal LeaveType1 = 0;
            Decimal LeaveType2 = 0;
            Decimal LeaveType3 = 0;
            foreach (OLTS.MODEL.OTS_TIME_SHEET sheet in sheets)
            {
                foreach (OLTS.MODEL.OTS_WORK_DETAIL det in sheet.OTS_WORK_DETAIL)
                {
                    if (sheet.DATE < new DateTime(year, month, Convert.ToInt16(temp[1])) || sheet.DATE > new DateTime(year, month, Convert.ToInt16(temp[2])))
                    {
                        continue;
                    }
                    SumAllTime += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                    if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                    {
                        str += "<tr><td style='text-align:center; border: 1px solid black'>" + site.PROJECT_NAME + "</td><td style='text-align:center; border: 1px solid black'>" + det.JOB_CODE + "</td><td style='text-align:center; border: 1px solid black'>" + det.DETAIL + "</td><td style='text-align:center; border: 1px solid black'>" + det.AMOUNT_OF_TIME + "</td><td style='background-color: gray; border: 1px solid black;'  ></td>   ";
                    }
                    if ((Convert.ToInt32(temp[2]) - 4) > 0)
                    {
                        if (Convert.ToInt32(temp[2]) - 4 == Convert.ToDateTime(sheet.DATE).Day)
                        {
                            if (sheet.LEAVE_ID.ToString().Trim().Length > 0)
                            {
                                LeaveAllTime += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                LeaveDay1 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "On Leave")
                                {
                                    LeaveType1 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Annual Holiday")
                                {
                                    LeaveType2 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Public Holiday")
                                {
                                    LeaveType3 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                continue;
                            }
                            str += "  <td style='text-align:center; border: 1px solid black'>" + det.AMOUNT_OF_TIME + "</td>   ";
                            sumDay1 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                        }
                        else
                        {
                            if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                            {
                                str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                            }
                        }
                    }
                    else
                    {
                        if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                        {
                            str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                        }
                    }

                    if ((Convert.ToInt32(temp[2]) - 3) > 0)
                    {
                        if (Convert.ToInt32(temp[2]) - 3 == Convert.ToDateTime(sheet.DATE).Day)
                        {
                            if (sheet.LEAVE_ID.ToString().Trim().Length > 0)
                            {
                                LeaveAllTime += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                LeaveDay2 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "On Leave")
                                {
                                    LeaveType1 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Annual Holiday")
                                {
                                    LeaveType2 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Public Holiday")
                                {
                                    LeaveType3 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                continue;
                            }
                            str += "  <td style='text-align:center; border: 1px solid black'>" + det.AMOUNT_OF_TIME + "</td>   ";
                            sumDay2 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                        }
                        else
                        {
                            if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                            {
                                str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                            }
                        }
                    }
                    else
                    {
                        if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                        {
                            str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                        }
                    }
                    if ((Convert.ToInt32(temp[2]) - 2) > 0)
                    {
                        if (Convert.ToInt32(temp[2]) - 2 == Convert.ToDateTime(sheet.DATE).Day)
                        {
                            if (sheet.LEAVE_ID.ToString().Trim().Length > 0)
                            {
                                LeaveAllTime += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                LeaveDay3 += Convert.ToDecimal(det.AMOUNT_OF_TIME);

                                if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "On Leave")
                                {
                                    LeaveType1 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Annual Holiday")
                                {
                                    LeaveType2 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Public Holiday")
                                {
                                    LeaveType3 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                continue;
                            }
                            str += "  <td style='text-align:center; border: 1px solid black'>" + det.AMOUNT_OF_TIME + "</td>   ";
                            sumDay3 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                        }
                        else
                        {
                            if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                            {
                                str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                            }
                        }
                    }
                    else
                    {
                        if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                        {
                            str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                        }
                    }
                    if ((Convert.ToInt32(temp[2]) - 1) > 0)
                    {
                        if (Convert.ToInt32(temp[2]) - 1 == Convert.ToDateTime(sheet.DATE).Day)
                        {
                            if (sheet.LEAVE_ID.ToString().Trim().Length > 0)
                            {
                                LeaveAllTime += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                LeaveDay4 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "On Leave")
                                {
                                    LeaveType1 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Annual Holiday")
                                {
                                    LeaveType2 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Public Holiday")
                                {
                                    LeaveType3 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                                }
                                continue;
                            }
                            str += "  <td style='text-align:center; border: 1px solid black'>" + det.AMOUNT_OF_TIME + "</td>   ";
                            sumDay4 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                        }
                        else
                        {
                            if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                            {
                                str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                            }
                        }
                    }
                    else
                    {
                        if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                        {
                            str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                        }
                    }

                    if (Convert.ToInt32(temp[2]) == Convert.ToDateTime(sheet.DATE).Day)
                    {
                        if (sheet.LEAVE_ID.ToString().Trim().Length > 0)
                        {
                            LeaveAllTime += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                            LeaveDay5 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                            if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "On Leave")
                            {
                                LeaveType1 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                            }
                            else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Annual Holiday")
                            {
                                LeaveType2 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                            }
                            else if (sheet.OTS_LEAVE.TYPE.ToString().Trim() == "Public Holiday")
                            {
                                LeaveType3 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                            }
                            continue;
                        }
                        str += "  <td style='text-align:center; border: 1px solid black'>" + det.AMOUNT_OF_TIME + "</td>   ";
                        sumDay5 += Convert.ToDecimal(det.AMOUNT_OF_TIME);
                    }
                    else
                    {
                        if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                        {
                            str += "  <td style='text-align:center; border: 1px solid black'> </td> ";
                        }
                    }
                    if (sheet.LEAVE_ID.ToString().Trim().Length == 0)
                    {
                        str += "  <td style='text-align:center; border: 1px solid black; background-color: gray;'> </td> <td style='text-align:left; border: 1px solid black'>" + det.REMARK + " </td> </tr> ";
                    }
                }
            }
            str += "<tr><td ></td><td></td><td style='text-align:center; border: 1px solid black; font-weight: bold; '>Total of Project</td><td style='text-align:center;  border: 1px solid black;'>" + SumAllTime + "</td><td style='background-color: gray; text-align:center; border: 1px solid black'></td> <td style='text-align:center; border: 1px solid black'>" + sumDay1 + "</td>  <td style='text-align:center; border: 1px solid black'>" + sumDay2 + "</td><td style='text-align:center; border: 1px solid black'>" + sumDay3 + "</td><td style='text-align:center; border: 1px solid black'>" + sumDay4 + "</td><td style='text-align:center; border: 1px solid black'>" + sumDay5 + "</td><td  style='background-color: gray; text-align:center; border: 1px solid black'></td> </tr> ";

            str += "<tr><td style='text-align:center; border: 1px solid black;'>On Leave</td>       <td style='text-align:center; border: 1px solid black;'>OL</td><td style='border: 1px solid black;' ></td><td style='text-align:center; border: 1px solid black;'>" + LeaveType1 + "</td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black;'></td>  <td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black;'></td> </tr> ";
            str += "<tr><td style='text-align:center; border: 1px solid black;'>Annual Holiday</td'><td style='text-align:center; border: 1px solid black;'>AH</td><td style='border: 1px solid black;' ></td><td style='text-align:center; border: 1px solid black;'>" + LeaveType2 + "</td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black;'></td>  <td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black;'></td> </tr>  ";
            str += "<tr><td style='text-align:center; border: 1px solid black;'>Public Holiday</td> <td style='text-align:center; border: 1px solid black;'>PH</td><td style='border: 1px solid black;' ></td><td style=' text-align:center; border: 1px solid black;'>" + LeaveType3 + "</td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black;'></td>  <td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td><td style='border: 1px solid black;'></td><td style='border: 1px solid black; background-color: gray;'></td><td style='border: 1px solid black;'></td> </tr>  ";
            str += "<tr><td ></td><td></td><td style='text-align:center; border: 1px solid black; font-weight: bold; '>Total of Leave</td><td style='text-align:center;  border: 1px solid black;'></td><td style='background-color: gray; text-align:center; border: 1px solid black'></td> <td style='text-align:center; border: 1px solid black'>" + LeaveDay1 + "</td>  <td style='text-align:center; border: 1px solid black'>" + LeaveDay2 + "</td><td style='text-align:center; border: 1px solid black'>" + LeaveDay3 + "</td><td style='text-align:center; border: 1px solid black'>" + LeaveDay4 + "</td><td style='text-align:center; border: 1px solid black'>" + LeaveDay5 + "</td><td  style='background-color: gray; text-align:center; border: 1px solid black'></td><td style='border: 1px solid black;'></td>  </tr> ";
            str += "<tr><td ></td><td></td><td style='text-align:center; border: 1px solid black; font-weight: bold; '>Grand Total   </td><td style='text-align:center;  border: 1px solid black;'>0</td><td style='background-color: gray; text-align:center; border: 1px solid black'></td> <td style='text-align:center; border: 1px solid black'>" + (LeaveDay1 + sumDay1) + "</td> <td style='text-align:center; border: 1px solid black'>" + (LeaveDay2 + sumDay2) + "</td><td style='text-align:center; border: 1px solid black'>" + (LeaveDay3 + sumDay3) + "</td><td style='text-align:center; border: 1px solid black'>" + (LeaveDay4 + sumDay4) + "</td><td style='text-align:center; border: 1px solid black'>" + (LeaveDay5 + sumDay5) + "</td><td  style='background-color: gray; text-align:center; border: 1px solid black'></td> <td style='border: 1px solid black;'></td>  </tr> ";

            str += "<tr><td></td><td></td><td></td><td></td><td></td><td></td>  <td></td><td></td><td></td><td></td><td></td><td></td> </tr> ";
            str += "<tr><td style='font-weight: bold; '>COMPLETE BY:</td><td></td><td></td><td></td><td style='font-weight: bold; '> APPROVED BY: </td><td></td>  <td></td><td></td><td></td><td></td><td></td><td></td> </tr> ";
            str += "<tr><td></td><td></td><td></td><td></td><td></td><td></td>  <td></td><td></td><td></td><td></td><td></td><td></td> </tr> ";
            str += "<tr><td></td><td></td><td></td><td></td><td></td><td></td>  <td></td><td></td><td></td><td></td><td></td><td></td> </tr> ";
            str += "<tr><td colspan='4' style='text-align:center;' >____________________</td><td></td><td></td>  <td colspan='4' style='text-align:center;' >____________________</td><td></td><td></td> </tr> ";
            str += "<tr><td colspan='4' style='text-align:center;' >" + site.STAFF_NAME_TH + "</td><td></td><td></td>  <td colspan='4' style='text-align:center;'>" + site.PROJECT_NAME + "</td><td></td><td></td> </tr> ";
            str += "<tr><td colspan='4' style='text-align:center;' >" + getFullMonthDate(DateTime.Now) + "</td><td></td><td></td>  <td colspan='4' style='text-align:center;'>" + getFullMonthDate(DateTime.Now) + "</td><td></td><td></td> </tr> ";
            str += " </table>";
            Export_file(str, "Co9_" + site.STAFF_NAME_EN);

        }

        private string getWorkingDateCube(DateTime dt)
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("th-TH");
            var day = dt.Day;
            var month = dt.ToString("MMM");
            var year = dt.Year;
            return dt.ToString("dd-MMM-yy"); //day + " " + month + " " + year;
        }

        private string getWorkingDate(DateTime dt)
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("th-TH");

            var day = dt.Day;
            var month = dt.ToString("MMM");
            var year = dt.Year;
            if (year < 2400)
            {
                year = year + 543;
            }
            return day + " " + month + " " + year + " ";
        }

        private string getFullMonthDate(int year, int month, int day)
        {

            DateTime dateTime;
            if (DateTime.DaysInMonth(year, month) < day)
            {
                if (month != 12)
                {
                    month++;
                }
                else
                {
                    month = 1;
                }
                day = 1;
            }
            if (day < 1)
            {
                if (month != 1)
                {
                    month--;
                }
                else
                {
                    month = 1;
                }
                day = DateTime.DaysInMonth(year, month);
            }
            dateTime = new DateTime(year, month, day);

            return dateTime.Day + " " + String.Format("{0:MMMM}", dateTime).ToString() + " " + dateTime.Year;
        }

        private string getFullMonthDate(DateTime dateTime)
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("th-TH");
            var dt = dateTime.ToString("yyyy");
            var day = dateTime.Day;
            var month = dateTime.ToString("MMMM");
            var year = dateTime.Year;
            if (year < 2400)
            {
                year = year + 543;
            }
            return day + " " + month + " " + year;
        }

        private void Export_file(string content, string FileName)
        {

            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename=Timesheet_" + FileName.Replace(" ", "_") + ".xls");
            Response.ContentType = "application/ms-excel";
            Response.ContentEncoding = System.Text.Encoding.Unicode;
            Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
            Response.Write(content);
            Response.End();
        }

        private void exportNew(Workbook book, string FileName)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Disposition", "attachment;filename=Timesheet_" + FileName.Replace(" ", "_") + ".xls");

            book.Save(Response.OutputStream);
            Response.End();
        }

        private void ExportExcel_New(byte[] pByte, string FileName)
        {
            try
            {
                //export excel
                Response.Clear();
                Response.Buffer = true;
                Response.ContentType = "application/vnd.ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment;filename=CubeofNine_Timesheet_" + FileName.Replace(" ", "_") + ".xls");
                Response.BinaryWrite(pByte);
                Response.End();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected void ddlYear_SelectedIndexChanged(object sender, EventArgs e)
        {
            loadTime();
        }

    }
}